#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
    public class JobLocationDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new JobLocation instance filled with the DataReader's current record data 
        /// </summary>
        private JobLocation GetJobLocationFromReader(IDataReader dr)
        {
            JobLocation obj = new JobLocation();
            obj.JobLocationID = (dr["JobLocationID"] is DBNull) ? Int32.MinValue : (Int32)dr["JobLocationID"];
            obj.JobLocationName = (dr["JobLocationName"] is DBNull) ? String.Empty : (String)dr["JobLocationName"];
            obj.JobLocationName_en = (dr["JobLocationName_en"] is DBNull) ? String.Empty : (String)dr["JobLocationName_en"];
            obj.CountryID = (dr["CountryID"] is DBNull) ? Int32.MinValue : (Int32)dr["CountryID"];
            obj.DisplayOrder = (dr["DisplayOrder"] is DBNull) ? -1 : (Int32)dr["DisplayOrder"];
            return obj;
        }
        #endregion

        /// <summary>
        /// Returns a collection of JobLocation with the data read from the input DataReader
        /// </summary>
        private List<JobLocation> GetJobLocationCollectionFromReader(IDataReader dr)
        {
            List<JobLocation> obj = new List<JobLocation>();
            while (dr.Read())
                obj.Add(GetJobLocationFromReader(dr));
            return obj;
        }

        public int Insert(JobLocation obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_JobLocation_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@JobLocationID", SqlDbType.Int).Direction = ParameterDirection.Output;
                myCommand.Parameters.Add("@JobLocationName", SqlDbType.NVarChar).Value = obj.JobLocationName;
                myCommand.Parameters.Add("@JobLocationName_en", SqlDbType.NVarChar).Value = obj.JobLocationName_en;
                myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = obj.CountryID;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;

                int ret = myCommand.ExecuteNonQuery();
                return (int)myCommand.Parameters["@JobLocationID"].Value;
            }
        }

        public bool Update(JobLocation obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_JobLocation_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@JobLocationID", SqlDbType.Int).Value = obj.JobLocationID;
                myCommand.Parameters.Add("@JobLocationName", SqlDbType.NVarChar).Value = obj.JobLocationName;
                myCommand.Parameters.Add("@JobLocationName_en", SqlDbType.NVarChar).Value = obj.JobLocationName_en;
                myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = obj.CountryID;
                myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(Int32 JobLocationID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_JobLocation_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@JobLocationID", SqlDbType.Int).Value = JobLocationID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the Job_JobLocation with the specified ID
        /// </summary>
        public JobLocation GetJobLocationByID(Int32 JobLocationID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_JobLocation_GetJobLocationByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@JobLocationID", SqlDbType.Int).Value = JobLocationID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetJobLocationFromReader(dr) : null);
            }
        }

        //Get All JobLocation
        public List<JobLocation> GetAllJobLocation(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_JobLocation_GetAllJobLocation", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetJobLocationCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  JobLocation
        public int GetCountJobLocation()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_JobLocation_GetCountJobLocation", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int)myCommand.ExecuteScalar();
            }
        }

        // Get JobLocationByCountryID
        public List<JobLocation> GetJobLocationByCountryID(Int32 CountryID, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_JobLocation_GetJobLocationByCountryID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = CountryID;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetJobLocationCollectionFromReader(dr);
            }
        }

        // Get JobLocationCountByCountryID
        public int GetJobLocationCountByCountryID(Int32 CountryID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                SqlCommand myCommand = new SqlCommand("Job_JobLocation_GetJobLocationCountByCountryID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CountryID", SqlDbType.Int).Value = CountryID;
                return (int)myCommand.ExecuteScalar();
            }
        }

    }
}
